﻿

CREATE PROCEDURE [dbo].[fares_GenereazaD394Articole]
	@CUI decimal(13,0),
	@DataStart datetime,
	@DataEnd datetime,
	@cumparari bit,
	@total decimal(20,0) OUT,	
	@totaltva decimal(20,0) OUT	
AS
BEGIN

	SET NOCOUNT ON;
	SELECT @total = sum(TotalValoare),@totaltva=SUM(TotalValoareTVA) FROM 
	(SELECT ROUND(Sum(c.ValoareTotala),0) as TotalValoare, ROUND(sum(c.ValoareTVA),0)  as TotalValoareTVA FROM 
	[Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener JOIN
	[DocumenteDetaliu] c ON a.[IDDocument]=c.IDDocument
	JOIN [RegimFiscal] r ON c.IDRegimFiscal = r.IDRegimFiscal
	JOIN [Articole] ar ON c.IDArticol = ar.IDArticol
	WHERE b.CUI = @CUI AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
	AND r.IntraIn394=1
	AND c.IDRegimFiscal=6 -- numai cele cu taxare inversa
	AND ar.CodArticol IN ('10011000', '10019010','10019091','10019099','10020000','100300','1005','120100','1205','120600','121291')
	GROUP BY ar.CodArticol) a


	SELECT MAX(ar.CodArticol) as CodPR,
	ROUND(Sum(c.ValoareTotala),0) as BazaPR, ROUND(sum(c.ValoareTotala*c.CotaTVA/100),0)  as TVAPR
	FROM 
	[Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener JOIN
	[DocumenteDetaliu] c ON a.[IDDocument]=c.IDDocument
	JOIN [RegimFiscal] r ON c.IDRegimFiscal = r.IDRegimFiscal
	JOIN [Articole] ar ON c.IDArticol = ar.IDArticol
	WHERE b.CUI = @CUI AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
	AND r.IntraIn394=1
	AND c.IDRegimFiscal=6 -- numai cele cu taxare inversa
	AND ar.CodArticol IN ('10011000', '10019010','10019091','10019099','10020000','100300','1005','120100','1205','120600','121291')
	GROUP BY ar.CodArticol
END